﻿using Dapper;
using SIG.DAL.Dapper.Model;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIG.DAL.Dapper.Repositories
{
    public class ProductRepository
    {
        private DbConnection _connection;

        public Product GetById(int id)
        {
            Product result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<Product>(id);
            }
            return result;
        }
        public int? Create(Product model)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
              
                    result = _connection.Insert(model);              
            }
            return result;
        }
        public int? Create(Product model, IEnumerable<Category> categories)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Insert(model);
                if (result > 0)
                {
                    if (categories!=null)
                    {
                        foreach (var item in categories)
                        {
                            string query = "INSERT INTO ProductCategory(ProductId,CategoryId)VALUES(@ProductId,@CategoryId)";
                            _connection.Execute(query, new { ProductId = result.Value, CategoryId = item.Id });
                        }
                    }
                                       
                }
                 
              
            }
            return result;
        }

        public int Update(Product model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Update(model);
            }
            return result;
        }
        public int Update(Product model, IEnumerable<Category> categories)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {               
                result = _connection.Update(model);
                if (result > 0)
                {
                    _connection.DeleteList<ProductCategory>(new { ProductId = model.Id });
                    if (categories != null)
                    {
                        foreach (var item in categories)
                        {
                            _connection.Execute("insert ProductCategory(ProductId, CategoryId) values(@ProductId, @CategoryId)", new { ProductId = model.Id, CategoryId = item.Id });
                            // _connection.Insert(new ProductCategory { ProductId = model.Id, CategoryId = item.Id });
                        }
                    }
                   
                }                           

            }
            return result;
        }
        public int Delete(Product model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<Product>(id);
            }
            return result;
        }

        public IEnumerable<Product> GetItems()
        {
            IEnumerable<Product> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Product>();
            }
            return result;
        }

        public IEnumerable<Product> GetActiveItems()
        {
            IEnumerable<Product> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Product>(new { Active = true });
            }
            return result;
        }

        public Product GetPrev(int id)
        {
            Product result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Product>( "where Active = 1 and Id < @Id", new { Id = id })
                    .OrderByDescending(m=>m.Id).FirstOrDefault();
            }
            return result;
        }
        public Product GetNext(int id)
        {
            Product result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Product>("where Active = 1 and Id > @Id", new { Id = id })
                    .OrderByDescending(m => m.Id).FirstOrDefault();
            }
            return result;
        }
        //public IEnumerable<Product> GetListPagedRecommendItems(int pageIndex, int pageSize, out int totalCount)
        //{
        //    IEnumerable<Product> result;
        //    using (_connection = Utilities.GetOpenConnection())
        //    {
        //        var sqlCount = "SELECT   COUNT(*) AS Expr1 FROM ProductCategory RIGHT JOIN  ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE(ProductCategory.CategoryId = @CategoryId) AND(ProductSet.Active = 1)";
        //        var sql = "SELECT  ProductSet.* FROM ProductCategory RIGHT JOIN ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE ProductCategory.CategoryId = @CategoryId AND ProductSet.Active = 1 AND ProductSet.Recommend = 1 ORDER BY ProductSet.CreatedDate DESC";

        //        totalCount = _connection.ExecuteScalar<int>(sqlCount, new { categoryId });
        //        result = _connection.Query<Product>(sql, new { categoryId }).ToList();
        //        // result = _connection.GetListPaged<Product>(pageIndex, pageSize, "where Active=1 and CategoryId = @CategoryId", "CreatedDate desc", new { CategoryId = categoryId });
        //    }
        //    return result;

        //}
        public IEnumerable<Product> GetListPagedRecommendItems(int pageIndex, int pageSize, int parentId, string keyword, out int totalCount)
        {
            IEnumerable<Product> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                var sqlCount = "SELECT COUNT(*) AS Expr1 FROM " +
                    "(SELECT DISTINCT  ProductSet.* FROM ProductCategory Right JOIN  ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE ProductSet.Active=1  AND ProductSet.Recommend = 1 ";

                var sql = "SELECT  * from (SELECT " +
                    "* , ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) AS RowNum FROM(SELECT DISTINCT  ProductSet.* " +
                    " FROM ProductCategory Right JOIN ProductSet ON " +
                     " ProductCategory.ProductId = ProductSet.Id WHERE ProductSet.Active=1 AND ProductSet.Recommend = 1 ";

                if (parentId > 0)
                {
                    sqlCount = sqlCount + " and ProductCategory.CategoryId = @CategoryId ";
                    sql = sql + " and ProductCategory.CategoryId = @CategoryId ";
                }
                if (!string.IsNullOrEmpty(keyword))
                {
                    keyword = keyword.Replace("[", "[[]").Replace("%", "[%]");
                    keyword = "%" + keyword + "%";

                    sqlCount = sqlCount + " and (ProductSet.ProductName like @Keyword or ProductSet.ProductNo like @Keyword or ProductSet.Body like @Keyword)";
                    sql = sql + " and (ProductSet.ProductName like @Keyword or ProductSet.ProductNo like @Keyword or ProductSet.Body like @Keyword) ";
                }
                sqlCount = sqlCount + ") AS RESULT";
                sql = sql + ") AS RESULT ) as tblproducts WHERE RowNum >= @StartIndex AND RowNum< @EndIndex ORDER BY RowNum";

                totalCount = _connection.ExecuteScalar<int>(sqlCount, new { Keyword = keyword, CategoryId = parentId });
                result = _connection.Query<Product>(sql, new { StartIndex = (pageIndex - 1) * pageSize + 1, EndIndex = pageIndex * pageSize + 1, Keyword = keyword, CategoryId = parentId }).ToList();

            }
            return result;

        }


        //public IEnumerable<Product> GetListPagedAcitveItems(int pageIndex, int pageSize, int categoryId, out int totalCount)
        //{
        //    IEnumerable<Product> result;
        //    using (_connection = Utilities.GetOpenConnection())
        //    {
        //        var sqlCount = "SELECT   COUNT(*) AS Expr1 FROM ProductCategory RIGHT JOIN  ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE(ProductCategory.CategoryId = @CategoryId) AND(ProductSet.Active = 1)";
        //        var sql = "SELECT  ProductSet.* FROM ProductCategory RIGHT JOIN ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE(ProductCategory.CategoryId = @CategoryId) AND (ProductSet.Active = 1) ORDER BY ProductSet.CreatedDate DESC";

        //        totalCount = _connection.ExecuteScalar<int>(sqlCount, new { categoryId });
        //        result = _connection.Query<Product>(sql, new { categoryId }).ToList();
        //       // result = _connection.GetListPaged<Product>(pageIndex, pageSize, "where Active=1 and CategoryId = @CategoryId", "CreatedDate desc", new { CategoryId = categoryId });
        //    }
        //    return result;

        //}
        //public IEnumerable<Product> GetListPagedAcitveItems(int pageIndex, int pageSize,  out int totalCount)
        //{
        //    IEnumerable<Product> result;
        //    using (_connection = Utilities.GetOpenConnection())
        //    {
        //        var sqlCount = "SELECT COUNT(*) AS Expr1 FROM ProductCategory INNER JOIN  ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE(ProductSet.Active = 1)";
        //        var sql = "SELECT  ProductSet.* FROM ProductCategory INNER JOIN ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE (ProductSet.Active = 1) ORDER BY dbo.ProductSet.CreatedDate DESC";

        //        totalCount = _connection.ExecuteScalar<int>(sqlCount);
        //        result = _connection.Query<Product>(sql).ToList();
        //        //totalCount = _connection.RecordCount<Product>("where Active=1");
        //        //result = _connection.GetListPaged<Product>(pageIndex, pageSize, "where Active=1", "CreatedDate desc");
        //    }
        //    return result;
        //}


        public IEnumerable<Product> GetListPagedAcitveItems(int pageIndex, int pageSize, int parentId, string keyword, out int totalCount)
        {
            IEnumerable<Product> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                var sqlCount = "SELECT COUNT(*) AS Expr1 FROM " +
                    "(SELECT DISTINCT  ProductSet.* FROM ProductCategory Right JOIN  ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE ProductSet.Active=1 ";

                var sql = "SELECT  * from (SELECT " +
                    "* , ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) AS RowNum FROM(SELECT DISTINCT  ProductSet.* " +
                    " FROM ProductCategory Right JOIN ProductSet ON " +
                     " ProductCategory.ProductId = ProductSet.Id WHERE ProductSet.Active=1 ";

                if (parentId > 0)
                {
                    sqlCount = sqlCount + " and ProductCategory.CategoryId = @CategoryId ";
                    sql = sql + " and ProductCategory.CategoryId = @CategoryId ";
                }
                if (!string.IsNullOrEmpty(keyword))
                {
                    keyword = keyword.Replace("[", "[[]").Replace("%", "[%]");
                    keyword = "%" + keyword + "%";

                    sqlCount = sqlCount + " and (ProductSet.ProductName like @Keyword or ProductSet.ProductNo like @Keyword or ProductSet.Body like @Keyword)";
                    sql = sql + " and (ProductSet.ProductName like @Keyword or ProductSet.ProductNo like @Keyword or ProductSet.Body like @Keyword) ";
                }
                sqlCount = sqlCount + ") AS RESULT";
                sql = sql + ") AS RESULT ) as tblproducts WHERE RowNum >= @StartIndex AND RowNum< @EndIndex ORDER BY RowNum";

                totalCount = _connection.ExecuteScalar<int>(sqlCount, new { Keyword = keyword, CategoryId = parentId });
                result = _connection.Query<Product>(sql, new { StartIndex = (pageIndex - 1) * pageSize + 1, EndIndex = pageIndex * pageSize + 1, Keyword = keyword, CategoryId = parentId }).ToList();

            }
            return result;

        }


        public IEnumerable<Product> GetListPagedItems(int pageIndex, int pageSize, int parentId, string keyword, out int totalCount)
        {
            IEnumerable<Product> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                var sqlCount = "SELECT COUNT(*) AS Expr1 FROM " +
                    "(SELECT DISTINCT  ProductSet.* FROM ProductCategory Right JOIN  ProductSet ON ProductCategory.ProductId = ProductSet.Id WHERE 1=1 ";

                var sql = "SELECT  * from (SELECT " +
                    "* , ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) AS RowNum FROM(SELECT DISTINCT  ProductSet.* "+
                    " FROM ProductCategory Right JOIN ProductSet ON " +
                     " ProductCategory.ProductId = ProductSet.Id WHERE 1 = 1 ";

                if (parentId > 0)
                {
                    sqlCount = sqlCount + " and ProductCategory.CategoryId = @CategoryId ";
                    sql = sql + " and ProductCategory.CategoryId = @CategoryId ";
                }
                if (!string.IsNullOrEmpty(keyword))
                {
                    keyword = keyword.Replace("[", "[[]").Replace("%", "[%]");
                    keyword = "%" + keyword + "%";

                    sqlCount = sqlCount + " and (ProductSet.ProductName like @Keyword or ProductSet.ProductNo like @Keyword or ProductSet.Body like @Keyword)";
                    sql = sql + " and (ProductSet.ProductName like @Keyword or ProductSet.ProductNo like @Keyword or ProductSet.Body like @Keyword) ";
                }
                sqlCount = sqlCount + ") AS RESULT";
                sql = sql + ") AS RESULT ) as tblproducts WHERE RowNum >= @StartIndex AND RowNum< @EndIndex ORDER BY RowNum";

                totalCount = _connection.ExecuteScalar<int>(sqlCount, new { Keyword = keyword, CategoryId = parentId });
                result = _connection.Query<Product>(sql, new { StartIndex= (pageIndex-1)* pageSize + 1, EndIndex = pageIndex * pageSize + 1,  Keyword = keyword, CategoryId = parentId }).ToList();
               
            }
            return result;

        }

    }

}
